package cn.com.libertymutual.sp.dao;

import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.com.libertymutual.sp.bean.TbWebRequestLog;

@Repository
public interface TbWebRequestLogDao  extends PagingAndSortingRepository<TbWebRequestLog, Integer>, JpaSpecificationExecutor<TbWebRequestLog> {

//	@Query("select t,count(t) from TbWebRequestLog t where t.requestTime > :startDate and t.requestTime < :endDate group by t.cityId")
//	List<TbWebRequestLog> findVisitCountByDate(@Param("startDate")String startDate, @Param("endDate")String endDate);

	@Query(value = "select t.*,count(CityId) from tb_web_requestlog t where t.requestTime between :startDate and  :endDate group by t.CityId",nativeQuery=true)
	List<TbWebRequestLog> findVisitCountByDate(@Param("startDate")String startDate, @Param("endDate")String endDate);
	
	@Query(value = "select t.*,count(*) from tb_web_requestlog t group by t.CityId",nativeQuery=true)
	List<TbWebRequestLog> findVisitCount();
	@Query(value="select DISTINCT(CityId)  from tb_web_requestlog where CityId is not NULL",nativeQuery=true)
	List<String> findDistinctCity();
	
//	@Query(value="select DISTINCT(CityId)  from tb_web_requestlog WHERE requestTime between :startDate and :endDate",nativeQuery=true)
//	List<String> findDistinctCity(@Param("startDate")String startDate, @Param("endDate")String endDate);

//	@Query(value="select * from tb_web_requestlog where id in (select max(id) from tb_web_requestlog where CityId = :CityId)",nativeQuery=true)
//	TbWebRequestLog findByCityId(@Param("CityId")String cityId);
	
	@Query(value="select * from tb_web_requestlog where  CityId = :CityId",nativeQuery=true)
	List<TbWebRequestLog> findByCityId(@Param("CityId")String CityId);

	@Query(value="select count(*)  from tb_web_requestlog where CityId = ?1 ",nativeQuery=true)
	int findCountByCityId(String cityId);

	@Query(value="select count(*)  from tb_web_requestlog where CityId = :CityId and requestTime between :startDate and :endDate",nativeQuery=true)
	int findCountByCityIdDate(@Param("CityId")String CityId,@Param("startDate")String startDate, @Param("endDate")String endDate);

	@Query(value="select CityId,longitude,Latitude,COUNT(CityId) from tb_web_requestlog WHERE requestTime between :startDate and :endDate GROUP BY CityId",nativeQuery=true)
	List<Object> findCity(@Param("startDate")String startDate, @Param("endDate")String endDate);
	
	@Query(value="select CityId,longitude,Latitude,COUNT(CityId) from tb_web_requestlog GROUP BY CityId",nativeQuery=true)
	List<Object> findCityall();
}
